package testy;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Database driver class for Users, Questions and Answers.
 * 
 * The database is opened by simple creating a new Data object. Then, all database operations
 * are performed on that object.
 * 
 * @author James Stump
 *
 * @version 12/8/14
 */
public class Data {
	private static final String DATABASE_NAME = "data.db";
	private static final String DATABASE_TEST_NAME = "test.db";

	private static final String TABLE_USERS = "table_users";
	private static final String TABLE_QUESTIONS = "table_questions";
	private static final String TABLE_ANSWERS = "table_answers";

	private static final String USERS_ID = "uid";
	private static final String USERS_PIN = "upin";
	private static final String USERS_PASSWORD = "upass";
	private static final String USERS_ADMIN = "uadmin";

	private static final String QUESTION_ID = "qid";
	private static final String QUESTION_TEXT = "qtext";
	private static final String QUESTION_IS_PROPOSED = "isproposed";

	private static final String ANSWER_ID = "aid";
	private static final String ANSWER_TEXT = "atext";
	private static final String ANSWER_QUESTION_KEY = "aqid";

	private Connection c;

	/**
	 * Creates a live connection to the database.
	 */
	public Data() {
		this(false);
	}

	/**
	 * Creates a connection to the database.
	 * 
	 * @param isTest
	 *            set true to create separate database for testing. Otherwise,
	 *            false for live database.
	 */
	public Data(boolean isTest) {
		c = null;
		try {
			Class.forName("org.sqlite.JDBC");
			if (isTest) {
				c = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_TEST_NAME);
				deleteAllTables();
			} else {
				c = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_NAME);
			}
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}

		setUpTables(isTest);
	}

	/**
	 * Inserts a user into the database.
	 * 
	 * @param userPin
	 *            Pin number the user will use to login
	 * @param userPassword
	 *            Password for the user account
	 * @param isAdmin
	 *            1 if user is an admin, 0 if not
	 */
	public void insertUser(final int userPin, final String userPassword,
			final int isAdmin) {
		PreparedStatement pst;
		String user = "INSERT INTO " + TABLE_USERS + " ( " + USERS_PIN + ", "
				+ USERS_PASSWORD + ", " + USERS_ADMIN + ")  " + " VALUES( "
				+ userPin + ", '" + userPassword + "', " + isAdmin + ")";
		try {
			pst = c.prepareStatement(user);
			pst.execute();
			pst.close();
		} catch (final Exception e) {
			System.out.println(e);
		}

	}

	/**
	 * Attempts to validate a uses information in the database.
	 * 
	 * Searches the database for a pin and password combination. Gets the user
	 * if the combination is valid or returns null if there is no valid pin and
	 * password combination.
	 * 
	 * @param thePin
	 *            Pin of the user trying to login
	 * @param thePass
	 *            Password of the user trying to login
	 * @return User object of user if successful, null if pin/password not found
	 *         in database
	 */
	public User tryLogin(int thePin, String thePass) {
		Statement stmt = null;
		User u = null;
		try {
			stmt = c.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM " + TABLE_USERS);
			while (rs.next()) {
				int pin = rs.getInt(USERS_PIN);
				String pass = rs.getString(USERS_PASSWORD);
				boolean isAdmin = true;
				if (rs.getInt(USERS_ADMIN) == 0) {
					isAdmin = false;
				}
				if (thePin == pin && thePass.equals(pass)) {
					u = new User(pin, pass, isAdmin);
				}
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return u;
	}

	/**
	 * Inserts a question into the database.
	 * 
	 * @param theQuestion
	 *            Question object of new question
	 */
	public void insertQuestion(final Question theQuestion) {
		insertQuestionTable(theQuestion, false);
	}

	/**
	 * Inserts a proposed question into the database.
	 * 
	 * @param theQuestion
	 *            Question object of new question
	 */
	public void insertProposedQuestion(final Question theQuestion) {
		insertQuestionTable(theQuestion, true);
	}

	/**
	 * Searches the database for a question and returns a List of questions that
	 * match search.
	 * 
	 * @param search
	 *            String to search database for
	 * @return List of Question objects for results or an empty List<Question> if no questions were
	 *         found
	 */
	public List<Question> searchQuestions(final String search) {
		List<Question> q = new ArrayList<>();
		Statement stmt = null;
		try {
			String sql = "SELECT * FROM " + TABLE_QUESTIONS + " WHERE "
					+ buildWhere(search) + " AND " + QUESTION_IS_PROPOSED
					+ " = 0";

			stmt = c.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				final int qid = rs.getInt(QUESTION_ID);
				q.add(getQuestion(qid));
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}

		return q;
	}
	
	/**
	 * Gets a list of all proposed questions in the database.
	 * 
	 * @return List<Question> of all current proposed questions
	 */
	public List<Question> allProposed() {
		List<Question> q = new ArrayList<>();
		Statement stmt = null;
		try {
			stmt = c.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT * FROM " + TABLE_QUESTIONS
							+ " WHERE " + QUESTION_IS_PROPOSED + " != 0");
			while (rs.next()) {
				final int qid = rs.getInt(QUESTION_ID);
				q.add(getQuestion(qid));
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}

		return q;
	}
	
	/**
	 * Turns a proposed question/answer into a normal question/answer.
	 * 
	 * @param qid id of proposed question to update
	 */
	public void updateProposed(int qid) {
		PreparedStatement pst;
		String sql = "UPDATE " + TABLE_QUESTIONS 
				+ " SET " + QUESTION_IS_PROPOSED + " = 0 "
				+ " WHERE " + QUESTION_ID + " = " + qid;
		try {
			pst = c.prepareStatement(sql);
			pst.execute();
			pst.close();
		} catch (final Exception e) {
			System.out.println(e);
		}
	}
	
	/**
	 * Turns a proposed question/answer into a normal question/answer.
	 * 
	 * @param qid id of proposed question to update
	 */
	public void deleteProposed(int qid) {
		PreparedStatement pst;
		String sql = "DELETE * FROM " +  TABLE_QUESTIONS + " WHERE " + QUESTION_ID + " = " + qid;
		try {
			pst = c.prepareStatement(sql);
			pst.execute();
			pst.close();
		} catch (final Exception e) {
			System.out.println(e);
		}
	}



	/* PRIVATE METHODS */

	/**
	 * Inserts an answer into the answers table.
	 * 
	 * @param theAnswer Answer object to insert into table
	 * @param qID ID of the question the answer relates to
	 */
	private void insertAnswer(final Answer theAnswer, final int qID) {
		PreparedStatement pst;
		String sql = "INSERT INTO " + TABLE_ANSWERS + " (" + ANSWER_TEXT + ", "
				+ ANSWER_QUESTION_KEY + ")" + " VALUES( '"
				+ theAnswer.getText() + "', " + qID + ")";
		try {
			pst = c.prepareStatement(sql);
			pst.execute();
			pst.close();
		} catch (final Exception e) {
			System.out.println(e);
		}
	}

	/**
	 * Builds the 'WHERE' clause to search a string. 
	 * 
	 * Breaks down the string by spaces
	 * 
	 * @param search String to search table for
	 * @return WHERE clause for the SQL statement
	 */
	private String buildWhere(final String search) {
		StringBuilder sb = new StringBuilder();
		String[] splitString = search.split(" ");
		sb.append(QUESTION_TEXT + " LIKE '%" + splitString[0].trim() + "%'");
		for (int i = 1; i < splitString.length; i++) {
			sb.append(" OR " + QUESTION_TEXT + " LIKE '%"
					+ splitString[i].trim() + "%'");
		}

		return sb.toString();
	}

	/**
	 * Creates the users table.
	 */
	private void createUserTable() {
		PreparedStatement pst;

		String sql = "CREATE TABLE " + TABLE_USERS + " (" + USERS_ID
				+ " integer primary key autoincrement, " + USERS_PIN
				+ " int not null unique, " + USERS_PASSWORD
				+ " TEXT not null, " + USERS_ADMIN + " int not null)";
		try {
			pst = c.prepareStatement(sql);
			pst.executeUpdate();
			pst.close();
		} catch (final Exception e) {
			System.out.println(e);
		}
	}

	/**
	 * Inserts a question into the database.
	 * 
	 * @param theQuestion
	 *            Question object of new question
	 * @param isProposed
	 *            True if you want to insert as a proposed question, false if
	 *            not
	 */
	private void insertQuestionTable(final Question theQuestion,
			boolean isProposed) {
		PreparedStatement pst;
		int isP = 0;
		if (isProposed) {
			isP = 1;
		}
		String sql = "INSERT INTO " + TABLE_QUESTIONS + " (" + QUESTION_TEXT
				+ ", " + QUESTION_IS_PROPOSED + ")" + "VALUES( '"
				+ theQuestion.getText() + "', " + isP + ")";
		try {
			pst = c.prepareStatement(sql);
			pst.execute();
			pst.close();
		} catch (final Exception e) {
			System.out.println(e);
		}

		final int atId = lastInsertID();

		for (Answer a : theQuestion.getAnswers()) {
			insertAnswer(a, atId);
		}
	}

	/**
	 * Finds the ID of the last row inserted into the database (in any tables).
	 * 
	 * @return id of last row inserted
	 */
	private int lastInsertID() {
		Statement stmt = null;
		try {
			stmt = c.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()");
			while (rs.next()) {
				return (int) rs.getInt(1);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return -1;
	}

	/**
	 * Creates the questions table.
	 */
	private void createQuestionTable() {
		PreparedStatement pst;

		String sql = "CREATE TABLE " + TABLE_QUESTIONS + " "
				+ "(" + QUESTION_ID + " integer primary key autoincrement, " 
				+ " " + QUESTION_TEXT + " TEXT not null," 
				+ QUESTION_IS_PROPOSED + " INT not null)";
		try {
			pst = c.prepareStatement(sql);
			pst.executeUpdate();
			pst.close();
		} catch (final Exception e) {
			System.out.println(e);
		}
	}

	/**
	 * Creates the answers table.
	 */
	private void createAnswerTable() {
		PreparedStatement pst;

		String sql = "Create table " + TABLE_ANSWERS 
				+ " (" + ANSWER_ID + " integer primary key autoincrement, " 
				+ " " + ANSWER_TEXT + " TEXT not null, " 
				+ " " + ANSWER_QUESTION_KEY + " INT not null)";
		try {
			pst = c.prepareStatement(sql);
			pst.executeUpdate();
			pst.close();
		} catch (final Exception e) {
			System.out.println(e);
		}
	}

	/**
	 * Checks if the user tables exists in the database.
	 * 
	 * @return true if the table exists, false if not
	 */
	private boolean checkTables() {
		DatabaseMetaData md;
		try {
			md = c.getMetaData();
			ResultSet rs = md.getTables(null, null, "%", null);
			while (rs.next()) {
				if (rs.getString(3).equals(TABLE_USERS)) {
					return true;
				}
			}
		} catch (SQLException e) {
			return false;
		}
		return false;
	}

	private List<Question> allQuestions() {
		List<Question> q = new ArrayList<>();
		Statement stmt = null;
		try {
			stmt = c.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT * FROM " + TABLE_QUESTIONS);
			while (rs.next()) {
				final int qid = rs.getInt(QUESTION_ID);
				q.add(getQuestion(qid));
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}

		return q;
	}

	/**
	 * Gets a question, given a question ID, from the database.
	 * 
	 * @param qid
	 *            id of question to get
	 * @return Question object
	 */
	private Question getQuestion(int qid) {
		Statement stmt = null;
		try {
			stmt = c.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM " + TABLE_QUESTIONS
					+ " JOIN " + TABLE_ANSWERS + " ON " + TABLE_QUESTIONS + "."
					+ QUESTION_ID + " = " + TABLE_ANSWERS + "."
					+ ANSWER_QUESTION_KEY + " " + " WHERE " + TABLE_QUESTIONS
					+ "." + QUESTION_ID + " = " + qid);
			List<Answer> a = new ArrayList<>();
			String q = null;
			int id = -1;
			while (rs.next()) {
				if (q == null) {
					q = rs.getString(QUESTION_TEXT);
					id = rs.getInt(QUESTION_ID);
				}
				a.add(new Answer(rs.getString(ANSWER_TEXT)));
			}
			if (a.isEmpty()) {
				stmt = c.createStatement();
				rs = stmt.executeQuery("SELECT * FROM " + TABLE_QUESTIONS
						+ " WHERE " + TABLE_QUESTIONS + "." + QUESTION_ID
						+ " = " + qid);
				rs.next();
				q = rs.getString(QUESTION_TEXT);
				id = rs.getInt(QUESTION_ID);
			}

			rs.close();
			stmt.close();
			return new Question(q, a, id);
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return null;
	}

	/**
	 * Drops all tables in the current database.
	 */
	private void deleteAllTables() {
		deleteTable(TABLE_QUESTIONS);
		deleteTable(TABLE_ANSWERS);
		deleteTable(TABLE_USERS);
		
	}

	/**
	 * Drops a table given its name.
	 * 
	 * @param tableName Name of table to drop
	 */
	private void deleteTable(final String tableName) {
		PreparedStatement pst;
		String user = "DROP TABLE " + tableName;
		
		try {
			pst = c.prepareStatement(user);
			pst.execute();
			pst.close();
		} catch (final Exception e) {
			System.out.println(e);
		}

	}

	/**
	 * Creates tables if they aren't created already.
	 * 
	 * If it's not a test database, populate the data with default values
	 * 
	 * @param isTest True if setting up test database, false if not
	 */
	private void setUpTables(boolean isTest) {
		if (!checkTables()) {
			createUserTable();
			createQuestionTable();
			createAnswerTable();
			if (!isTest) {
				addUsers();	
				addQuestions();
			}
		}
	}

	/**
	 * Adds default users to the database.
	 */
	private void addUsers() {
		insertUser(1234, "4321", 0);
		insertUser(4321, "1234", 1);

	}

	/**
	 * Adds default question/answer pairs to the database.
	 */
	private void addQuestions() {
		insertQuestion(new Question(
				"Does your company have a policy regarding use of vendors who are Woman or Minority Owned Businesses?",
				"process * woman * minority",
				"Global Business Logistics actively solicits diversity in our service providers. The spirit of these efforts is to help emerging firms in the industry gain a greater presence while at the same time adding diversity among providers."));
		insertQuestion(new Question("Where is your management team based?",
				"freight team location",
				"Global Business Logisticss management team is based in Seattle, Washington."));
		insertQuestion(new Question(
				"How many employees are there in your company?",
				"amount staff firm",
				"Global Business Logistics operates from a network of over 100 locations and employ over 7,000 trained and experienced professionals."));
		insertQuestion(new Question(
				"Provide a brief overview of your company and history of your organization.",
				"brief overview firm history",
				"Established in 1979, Global Business Logistics is headquartered in Seattle, Washington and provides logistics and freight forwarding services throughout the world."));
		insertQuestion(new Question("When was the firms inception date?",
				"firm inception date",
				"Global Business Logistics began providing services in 1979."));
		insertQuestion(new Question(
				"How long has your company been in business?",
				"length * firm",
				"Established in 1979, Global Business Logistics provides logistics and freight forwarding services throughout the world."));
	}

}